package com.excel;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.img.Img;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.pojo.Dictionary;
import com.pojo.*;
import com.service.*;
import com.util.*;
import com.vo.BusinessVo;
import com.vo.ChongzhiVo;
import com.vo.StaffVO;
import com.vo.XiaofeiVo;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Label;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.web.multipart.MultipartFile;

import javax.swing.*;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

    @Autowired
    public static QiyeService qiyeService;
    @Autowired
    public static WuyeService wuyeService;

    @Autowired
    public static StaffService staffService;

    @Autowired
    public static StaffHistoryService staffHistoryService;

    @Autowired
    public static DictionaryService dictionaryService;

    @Autowired
    public static BusinessService businessService;

    @Autowired
    public static StaffQiyeChangeRecordService staffQiyeChangeRecordService;

    @Autowired
    @Qualifier("redisTemplate")
    public static RedisTemplate redisTemplate ;


    public static void exportExcelByCz(String fileName, String sheetName, List resultList, String type) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;

        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        //设置title
        List<String> titles = new ArrayList();
        titles.add("编号");
        titles.add("姓名");
        titles.add("联系方式");
        titles.add("员工卡号");
        if (type == "cz") {
            titles.add("充值金额");
            titles.add("充值时间");
        }else if (type == "xf"){
            titles.add("消费金额");
            titles.add("消费时间");
        }

        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);


        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            if (type == "cz") {
                ChongzhiVo cz = (ChongzhiVo) resultList.get(i - 1);
                getParam(sheet, colmStyle, i, cz.getStaffName(), cz.getStaffBh(), cz.getStaffPhone(), cz.getCardNo());
                label=new Label(4,i,cz.getCzAmount().toString() , colmStyle);
                sheet.addCell(label);
                label=new Label(5,i,sdf.format(cz.getCzTime()) , colmStyle);
                sheet.addCell(label);
            }else if (type == "xf") {
                XiaofeiVo xf = (XiaofeiVo) resultList.get(i - 1);
                getParam(sheet, colmStyle, i, xf.getStaffName(), xf.getStaffBh(), xf.getStaffPhone(), xf.getCardNo());
                label=new Label(4,i,xf.getXfAmount().toString() , colmStyle);
                sheet.addCell(label);
                label=new Label(5,i,sdf.format(xf.getXfTime()) , colmStyle);
                sheet.addCell(label);
            }
        }
        setColumnAutoSize(sheet,10);

        workbook.write();
        workbook.close();
    }

    private static void getParam(WritableSheet sheet, WritableCellFormat colmStyle, int i, String staffName, String staffBh, String staffPhone, String cardNo) throws WriteException {
        Label label;
        label=new Label(0,i, staffBh,colmStyle);
        sheet.addCell(label);
        label=new Label(1,i, staffName, colmStyle);
        sheet.addCell(label);
        label=new Label(2,i, staffPhone, colmStyle);
        sheet.addCell(label);
        label=new Label(3,i, cardNo, colmStyle);
        sheet.addCell(label);
    }

    public static void exportExcelByBusiness(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("商户ID");
        titles.add("商户名称/卡口");
        titles.add("商户地址");
        titles.add("业主姓名");
        titles.add("物业");
        titles.add("密码");
        titles.add("备注");


        Label label=null;

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                BusinessVo business = (BusinessVo) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, business.getBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, business.getName(),colmStyle);
                sheet.addCell(label);
                label=new Label(3,i + 1, business.getAddress(), colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, business.getFzr(), colmStyle);
                sheet.addCell(label);
                label=new Label(5,i + 1, business.getWyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(6,i + 1, "123456", colmStyle);
                sheet.addCell(label);
                label=new Label(7,i + 1, business.getMessage(), colmStyle);
                sheet.addCell(label);
            }
            setColumnAutoSize(sheet,10);
        }


        workbook.write();
        workbook.close();
    }

//    public static void exportExcelByHesuan(String fileName, String sheetName, List resultList) throws Exception {
//        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
//        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
//        WritableCellFormat wcf = new WritableCellFormat(wf);
//        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
//        //设置title
//        List<String> titles = new ArrayList();
//        titles.add("序号");
//        titles.add("员工编号");
//        titles.add("员工姓名");
//        titles.add("国籍");
//        titles.add("性别");
//        titles.add("所属物业");
//        titles.add("所属企业");
//        titles.add("医生姓名");
//        titles.add("检测结果");
//        titles.add("检测提交时间");
//        titles.add("检测结果时间");
//        titles.add("条形码");
//
//        Label label=null;
//        int iMaxLines = 60000;
//        int totalLines = resultList.size();
//        int page = totalLines / iMaxLines;
//        int lastLines = totalLines % iMaxLines;//尾页要写的行数
//        if (totalLines == 0) {
//            return;
//        }
//        if (page >= 1 && lastLines > 0) {
//            page = page + 1;
//        }
//        if(page == 0){
//            page = 1;
//        }
//
//        for(int p = 0; p < page; p ++){
//            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
//            int iStartRow = 0;
//            int iEndRow = 0;
//            if (p == 0) {
//                iStartRow = 0;
//                if (page == 1) {
//                    iEndRow = totalLines - 1;
//                } else {
//                    iEndRow = (p + 1) * iMaxLines - 1;
//                }
//            } else {
//                iStartRow = p * iMaxLines;
//                if (p == page - 1) {
//                    iEndRow = totalLines - 1;
//                } else {
//                    iEndRow = (p + 1) * iMaxLines - 1;
//                }
//            }
//            List listPageValue = new ArrayList();//保存每页的数据
//            for (int i = iStartRow; i <= iEndRow; i++) {
//                if (resultList.get(i) == null) {
//                    continue;
//                }
//                listPageValue.add(resultList.get(i));
//            }
//
//            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//            titleStyle.setBackground(Colour.SEA_GREEN);
//            titleStyle.setAlignment(Alignment.CENTRE);
//            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//            for(int i = 0; i < titles.size(); i ++){
//                //x,y,第一行的列名
//                label=new Label(i,0,titles.get(i),titleStyle);
//                //7：添加单元格
//                sheet.addCell(label);
//            }
//
//            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
////        title.setBackground(Colour.VERY_LIGHT_YELLOW);
//            colmStyle.setAlignment(Alignment.CENTRE);
//            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//            for(int i = 0; i < listPageValue.size(); i ++){
//                HesuanVo hesuan = (HesuanVo) listPageValue.get(i);
//                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
//                sheet.addCell(label);
//                label=new Label(1,i + 1, hesuan.getStaffBh(), colmStyle);
//                sheet.addCell(label);
//                label=new Label(2,i + 1, hesuan.getStaffName(),colmStyle);
//                sheet.addCell(label);
//                label=new Label(3,i + 1, hesuan.getStaffGj(),colmStyle);
//                sheet.addCell(label);
//                String sex = "";
//                if (hesuan.getStaffSex() == 0) {
//                    sex = "女";
//                }else {
//                    sex = "男";
//                }
//                label=new Label(4,i + 1, sex, colmStyle);
//                sheet.addCell(label);
//                label=new Label(5,i + 1, hesuan.getWyName(), colmStyle);
//                sheet.addCell(label);
//                label=new Label(6,i + 1, hesuan.getQyName(), colmStyle);
//                sheet.addCell(label);
//                label=new Label(7,i + 1, hesuan.getDoctorName(), colmStyle);
//                sheet.addCell(label);
//                String jcResult = "";
//                if (hesuan.getJcResult() == 1) {
//                    jcResult = "阴性";
//                }else if (hesuan.getJcResult() == 2) {
//                    jcResult = "阳性";
//                }else {
//                    jcResult = "未出结果";
//                }
//                label=new Label(8,i + 1, jcResult, colmStyle);
//                sheet.addCell(label);
//                label=new Label(9,i + 1, sdf.format(hesuan.getSubmitTime()), colmStyle);
//                sheet.addCell(label);
//
//                if (null == hesuan.getCreateTime()) {
//                    label=new Label(10,i + 1, "", colmStyle);
//                }else {
//                    label=new Label(10,i + 1, sdf.format(hesuan.getCreateTime()), colmStyle);
//                }
//                sheet.addCell(label);
//
//                if (ValidateHelper.isEmptyString(hesuan.getBarCode())) {
//                    label=new Label(11,i + 1, "", colmStyle);
//                }else {
//                    label=new Label(11,i + 1, hesuan.getBarCode(), colmStyle);
//                }
//                sheet.addCell(label);
//            }
//            setColumnAutoSize2(sheet,10);
//        }
//
//        workbook.write();
//        workbook.close();
//    }

    /**
     * 统计医生核酸检测人数
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
//    public static void exportExcelByHsCount(String fileName, String sheetName, List resultList) throws Exception {
//        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
//        WritableSheet sheet = workbook.createSheet(sheetName, 0);
//        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
//        WritableCellFormat wcf = new WritableCellFormat(wf);
//        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
//        //设置title
//        List<String> titles = new ArrayList();
//        titles.add("序号");
//        titles.add("医生姓名");
//        titles.add("核酸检测人数");
//
//        Label label=null;
//
//        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        titleStyle.setBackground(Colour.SEA_GREEN);
//        titleStyle.setAlignment(Alignment.CENTRE);
//        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
////        title.setBackground(Colour.VERY_LIGHT_YELLOW);
//        colmStyle.setAlignment(Alignment.CENTRE);
//        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        for(int i = 0;i < titles.size();i++){
//            //x,y,第一行的列名
//            label=new Label(i,0,titles.get(i),titleStyle);
//            //7：添加单元格
//            sheet.addCell(label);
//        }
//
//        for(int i = 1;i <= resultList.size();i++){
//            HesuanVo hesuan = (HesuanVo) resultList.get(i - 1);
//            label=new Label(0,i, String.valueOf(i),colmStyle);
//            sheet.addCell(label);
//            label=new Label(1,i, hesuan.getDoctorName(), colmStyle);
//            sheet.addCell(label);
//            label=new Label(2,i, hesuan.getHsCount().toString(),colmStyle);
//            sheet.addCell(label);
//        }
//        setColumnAutoSize(sheet,10);
//
//        workbook.write();
//        workbook.close();
//    }

    /**
     * 导出医生核酸检测人数详情
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
//    public static void exportExcelByHsCountDetail(String fileName, String sheetName, List resultList) throws Exception {
//        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
//        WritableSheet sheet = workbook.createSheet(sheetName, 0);
//        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
//        WritableCellFormat wcf = new WritableCellFormat(wf);
//        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
//        //设置title
//        List<String> titles = new ArrayList();
//        titles.add("序号");
//        titles.add("员工编号");
//        titles.add("员工姓名");
//        titles.add("国籍");
//        titles.add("性别");
//        titles.add("所属物业");
//        titles.add("医生姓名");
//        titles.add("检测结果");
//        titles.add("检测提交时间");
//        titles.add("检测结果时间");
//
//        Label label=null;
//
//        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        titleStyle.setBackground(Colour.SEA_GREEN);
//        titleStyle.setAlignment(Alignment.CENTRE);
//        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
////        title.setBackground(Colour.VERY_LIGHT_YELLOW);
//        colmStyle.setAlignment(Alignment.CENTRE);
//        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        for(int i = 0;i < titles.size();i++){
//            //x,y,第一行的列名
//            label=new Label(i,0,titles.get(i),titleStyle);
//            //7：添加单元格
//            sheet.addCell(label);
//        }
//
//        for(int i = 1;i <= resultList.size();i++){
//            HesuanVo hesuan = (HesuanVo) resultList.get(i - 1);
//            label=new Label(0,i, String.valueOf(i),colmStyle);
//            sheet.addCell(label);
//            label=new Label(1,i, hesuan.getStaffBh(), colmStyle);
//            sheet.addCell(label);
//            label=new Label(2,i, hesuan.getStaffName(),colmStyle);
//            sheet.addCell(label);
//            label=new Label(3,i, hesuan.getStaffGj(),colmStyle);
//            sheet.addCell(label);
//            String sex = "";
//            if (hesuan.getStaffSex() == 0) {
//                sex = "女";
//            }else {
//                sex = "男";
//            }
//            label=new Label(4,i, sex, colmStyle);
//            sheet.addCell(label);
//            label=new Label(5,i, hesuan.getWyName(), colmStyle);
//            sheet.addCell(label);
//            label=new Label(6,i, hesuan.getDoctorName(), colmStyle);
//            sheet.addCell(label);
//            String jcResult = "";
//            if (hesuan.getJcResult() == 1) {
//                jcResult = "阴性";
//            }else if (hesuan.getJcResult() == 2) {
//                jcResult = "阳性";
//            }else {
//                jcResult = "未出结果";
//            }
//            label=new Label(7,i, jcResult, colmStyle);
//            sheet.addCell(label);
//            label=new Label(8,i, sdf.format(hesuan.getSubmitTime()), colmStyle);
//            sheet.addCell(label);
//
//            if (null == hesuan.getCreateTime()) {
//                label=new Label(9,i, "", colmStyle);
//                sheet.addCell(label);
//            }else {
//                label=new Label(9,i, sdf.format(hesuan.getCreateTime()), colmStyle);
//                sheet.addCell(label);
//            }
//        }
//        setColumnAutoSize(sheet,9);
//
//        workbook.write();
//        workbook.close();
//    }

    /**
     * 导出员工
     * @param fileName
     * @param sheetName
     * @param resultList
     * @param upload 照片路径
     * @param exportType 是否带员工照片导出：1带，2不带
     * @return
     * @throws Exception
     */
    public static SXSSFWorkbook exportExcelByStaff(String fileName, String sheetName, List<Staff> resultList, String upload, Integer exportType) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook();

        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(HSSFColor.GREEN.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.DOTTED); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.DOTTED);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框

        Font font1 = workbook.createFont();
        font1.setFontName("微软雅黑");
        font1.setFontHeightInPoints((short) 11);
        font1.setBold(true);
        style.setFont(font1);

        CellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font2 = workbook.createFont();
        font2.setFontName("微软雅黑");
        font2.setFontHeightInPoints((short) 10);
        style2.setFont(font2);

        String[] excelHeader = {};
        if (null != exportType && exportType == 1) {
            excelHeader = new String[]{"序号", "职业", "物业公司", "公司（商铺）名字", "公司（商铺）地址", "姓名", "编号", "国籍", "性别", "出生年月", "入职时间", "密码", "备注", "照片"};
        }else {
            excelHeader = new String[]{"序号", "职业", "物业公司", "公司（商铺）名字", "公司（商铺）地址", "姓名", "编号", "国籍", "性别", "出生年月", "入职时间", "密码", "备注"};
        }

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return null;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++) {
            SXSSFSheet[] sheet = new SXSSFSheet[p + 1];
            sheet[p] = workbook.createSheet();
            sheet[p].createFreezePane(0, 1, 0, 1);
            workbook.setSheetName(p, "员工记录" + (p + 1));
            SXSSFDrawing patriarch = sheet[p].createDrawingPatriarch();
            SXSSFRow row = sheet[p].createRow(0);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            for (int i = 0; i < excelHeader.length; i++) {
                SXSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader[i]);
                cell.setCellStyle(style);
//                sheet[p].autoSizeColumn(i);
                //设置列宽
                sheet[p].setColumnWidth(i, 70 * 70);
                // sheet.SetColumnWidth(i, 100 * 256);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

            byte[] imgByte = null;//图片字节数组

            for (int i = 0; i < listPageValue.size(); i++) {
                Staff staff = (Staff) listPageValue.get(i);
                SXSSFRow titleRowNext = sheet[p].createRow(i + 1);
                titleRowNext.setHeight((short) (35.7 * 30));

                SXSSFCell cell0 = titleRowNext.createCell(0);
                cell0.setCellValue(String.valueOf(i + 1));
                cell0.setCellStyle(style2);

                if (ValidateHelper.isNotEmptyString(staff.getZhiye())) {
                    SXSSFCell cell1 = titleRowNext.createCell(1);
                    cell1.setCellValue(String.valueOf(staff.getZhiye()));
                    cell1.setCellStyle(style2);
                } else {
                    SXSSFCell cell1 = titleRowNext.createCell(1);
                    cell1.setCellValue(String.valueOf(""));
                    cell1.setCellStyle(style2);
                }

                if (staff.getQiye() == null) {
                    SXSSFCell cell2 = titleRowNext.createCell(2);
                    cell2.setCellValue("");
                    cell2.setCellStyle(style2);
                } else {
                    Wuye wuye = wuyeService.selectID(staff.getQiye().getWyID());
                    if (wuye == null) {
                        SXSSFCell cell2 = titleRowNext.createCell(2);
                        cell2.setCellValue("");
                        cell2.setCellStyle(style2);
                    } else {
                        SXSSFCell cell2 = titleRowNext.createCell(2);
                        cell2.setCellValue(wuye.getWyname());
                        cell2.setCellStyle(style2);
                    }
                }

                if (staff.getQiye() == null) {
                    SXSSFCell cell3 = titleRowNext.createCell(3);
                    cell3.setCellValue("");
                    cell3.setCellStyle(style2);
                } else {
                    SXSSFCell cell3 = titleRowNext.createCell(3);
                    cell3.setCellValue(String.valueOf(staff.getQiye().getQyname()));
                    cell3.setCellStyle(style2);
                }

                if (staff.getQiye() == null || ValidateHelper.isEmptyString(staff.getQiye().getAddress())) {
                    SXSSFCell cell4 = titleRowNext.createCell(4);
                    cell4.setCellValue("");
                    cell4.setCellStyle(style2);
                } else {
                    SXSSFCell cell4 = titleRowNext.createCell(4);
                    cell4.setCellValue(String.valueOf(staff.getQiye().getAddress()));
                    cell4.setCellStyle(style2);
                }


                SXSSFCell cell5 = titleRowNext.createCell(5);
                cell5.setCellValue(String.valueOf(staff.getName()));
                cell5.setCellStyle(style2);

                if (ValidateHelper.isEmptyString(staff.getBh())) {
                    SXSSFCell cell6 = titleRowNext.createCell(6);
                    cell6.setCellValue(String.valueOf(""));
                    cell6.setCellStyle(style2);
                } else {
                    SXSSFCell cell6 = titleRowNext.createCell(6);
                    cell6.setCellValue(String.valueOf(staff.getBh()));
                    cell6.setCellStyle(style2);
                }

                SXSSFCell cell7 = titleRowNext.createCell(7);
                cell7.setCellValue(String.valueOf(staff.getGuoji()));
                cell7.setCellStyle(style2);

                String sex = "";
                if (staff.getSex() == 1) {
                    sex = "男";
                } else {
                    sex = "女";
                }

                SXSSFCell cell8 = titleRowNext.createCell(8);
                cell8.setCellValue(String.valueOf(sex));
                cell8.setCellStyle(style2);

                SXSSFCell cell9 = titleRowNext.createCell(9);
                cell9.setCellValue(String.valueOf(staff.getCstime()));
                cell9.setCellStyle(style2);

                String rzTime = "";
                if (staff.getRztime() != null) {
                    rzTime = sdf.format(staff.getRztime());
                }
                SXSSFCell cell10 = titleRowNext.createCell(10);
                cell10.setCellValue(String.valueOf(rzTime));
                cell10.setCellStyle(style2);

                SXSSFCell cell11 = titleRowNext.createCell(11);
                cell11.setCellValue(String.valueOf("123456"));
                cell11.setCellStyle(style2);

                if (ValidateHelper.isEmptyString(staff.getMessage())) {
                    SXSSFCell cell12 = titleRowNext.createCell(12);
                    cell12.setCellValue(String.valueOf(""));
                    cell12.setCellStyle(style2);
                } else {
                    SXSSFCell cell12 = titleRowNext.createCell(12);
                    cell12.setCellValue(String.valueOf(staff.getMessage()));
                    cell12.setCellStyle(style2);
                }

                if (null != exportType && exportType == 1) {
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    BufferedImage bufferImg = null;
                    if (ValidateHelper.isNotEmptyString(staff.getPicture())) {
                        String imgUrl = upload + staff.getPicture();//图片地址
                        String suffix = imgUrl.substring(imgUrl.lastIndexOf(".") + 1);//图片后缀
                        try {
                            bufferImg = ImageUtil.rotaImage(imgUrl);
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        try {
                            //ImageIO.write(bufferImg, suffix, byteArrayOut);
                            Img.from(bufferImg).write(byteArrayOut);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        XSSFClientAnchor anchor =
                                new XSSFClientAnchor(0, 0, 0, 0, 13,i + 1, 14,i + 2);
                        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
                        patriarch.createPicture(anchor,workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
                    } else {
                        SXSSFCell cell13 = titleRowNext.createCell(13);
                        cell13.setCellValue(String.valueOf(""));
                        cell13.setCellStyle(style2);
                    }


//                    String str = host + "upload/" + staff.getPicture();
//                    imgByte = getFileStream(str);
//                    if (imgByte != null) {
//                        int addPicture;
//                        // 图片存在即输出图片
//                        //判断图片后缀
//                        String hz=str.substring(str.length() - 3);
//                        if("jpg".equals(hz)){
//                            addPicture = workbook.addPicture(imgByte, workbook.PICTURE_TYPE_JPEG);
//                        }else {
//                            addPicture = workbook.addPicture(imgByte, workbook.PICTURE_TYPE_PNG);
//                        }
//
//                        //图片位置 j是列，rowNum是行
//                        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 13,i + 1, 14,i + 2);
//                        Picture picture = patriarch.createPicture(anchor, addPicture);
//
//                    }

                }
            }
        }
        return workbook;
    }

    public static BufferedImage getBufferedImage(String  imgUrl) throws MalformedURLException {
        URL url = new URL(imgUrl);
        ImageIcon icon = new ImageIcon(url);
        Image image = icon.getImage();

        // 如果是从本地加载，就用这种方式，没亲自测试过
        // Image src=Toolkit.getDefaultToolkit().getImage(filePath);

        // This code ensures that all the pixels in the image are loaded
        BufferedImage bimage = null;
        GraphicsEnvironment ge = GraphicsEnvironment
                .getLocalGraphicsEnvironment();
        try {
            int transparency = Transparency.OPAQUE;
            GraphicsDevice gs = ge.getDefaultScreenDevice();
            GraphicsConfiguration gc = gs.getDefaultConfiguration();
            bimage = gc.createCompatibleImage(image.getWidth(null),
                    image.getHeight(null), transparency);
        } catch (HeadlessException e) {
            // The system does not have a screen
        }
        if (bimage == null) {
            // Create a buffered image using the default color model
            int type = BufferedImage.TYPE_INT_RGB;
            bimage = new BufferedImage(image.getWidth(null),
                    image.getHeight(null), type);
        }
        // Copy image to buffered image
        Graphics g = bimage.createGraphics();
        // Paint the image onto the buffered image
        g.drawImage(image, 0, 0, null);
        g.dispose();
        return bimage;
    }

    /**
     * 得到文件流
     * @param url
     * @return
     */
    public static byte[] getFileStream(String url){
        try {
            URL httpUrl = new URL(url);
            HttpURLConnection conn = (HttpURLConnection)httpUrl.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5 * 1000);
            InputStream inStream = conn.getInputStream();//通过输入流获取图片数据
            byte[] btImg = readInputStream(inStream);//得到图片的二进制数据
            return btImg;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从输入流中获取数据
     * @param inStream 输入流
     * @return
     * @throws Exception
     */
    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len = 0;
        while( (len=inStream.read(buffer)) != -1 ){
            outStream.write(buffer, 0, len);
        }
        inStream.close();
        return outStream.toByteArray();
    }

    public static void exportExcelByStaffTrail(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("编号");
        titles.add("姓名");
        titles.add("国籍");
        titles.add("所属物业");
        titles.add("所属企业");
        titles.add("性别");
        titles.add("联系方式");
        titles.add("家庭住址");
        titles.add("状态");
        titles.add("最后活动时间");
        titles.add("未活动天数");

        Label label=null;

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                StaffVO staff = (StaffVO) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, staff.getBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, staff.getName(),colmStyle);
                sheet.addCell(label);
                label=new Label(3,i + 1, staff.getGuoji(), colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, staff.getWyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(5,i + 1, staff.getQyName(), colmStyle);
                sheet.addCell(label);
                String sex = "";
                if (staff.getSex() == 1) {
                    sex = "男";
                }else {
                    sex = "女";
                }
                label=new Label(6,i + 1, sex, colmStyle);
                sheet.addCell(label);
                label=new Label(7,i + 1, staff.getPhone(), colmStyle);
                sheet.addCell(label);
                label=new Label(8,i + 1, staff.getAddress(), colmStyle);
                sheet.addCell(label);
                String status = "";
                if (staff.getStatus() == 1) {
                    status = "在职";
                }else {
                    status = "离职";
                }
                label=new Label(9,i + 1, status, colmStyle);
                sheet.addCell(label);
                String last_out_time = "";
                if (staff.getLast_out_time() != null) {
                    last_out_time = sdf.format(staff.getLast_out_time());
                }
                label=new Label(10,i + 1, last_out_time, colmStyle);
                sheet.addCell(label);
                if (staff.getWcmDays() == null) {
                    label=new Label(11,i + 1, "未活动过", colmStyle);

                }else {
                    label=new Label(11,i + 1, staff.getWcmDays() + "天", colmStyle);
                }
                sheet.addCell(label);
            }
            setColumnAutoSize2(sheet,10);
        }


        workbook.write();
        workbook.close();
    }


    public static void exportExcelByGp(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工编号");
        titles.add("姓名");
        titles.add("性别");
        titles.add("职业");
        titles.add("联系方式");
        titles.add("家庭住址");
        titles.add("证件号码");
        titles.add("所属物业");
        titles.add("所属企业");
        titles.add("是否制卡");
        titles.add("备注");


        Label label=null;

        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                StaffVO staff = (StaffVO) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, staff.getBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, staff.getName(),colmStyle);
                sheet.addCell(label);
                String sex = "";
                if (staff.getSex() == 1) {
                    sex = "男";
                }else {
                    sex = "女";
                }
                label=new Label(3,i + 1, sex, colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, staff.getZhiye(), colmStyle);
                sheet.addCell(label);
                label=new Label(5,i + 1, staff.getPhone(), colmStyle);
                sheet.addCell(label);
                label=new Label(6,i + 1, staff.getAddress(), colmStyle);
                sheet.addCell(label);
                label=new Label(7,i + 1, staff.getSfz(), colmStyle);
                sheet.addCell(label);
                label=new Label(8,i + 1, staff.getWyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(9,i + 1, staff.getQyName(), colmStyle);
                sheet.addCell(label);
                String zkName = "";
                if (null != staff.getZkFlag() && staff.getZkFlag() == 1) {
                    zkName = "已制卡";
                }else{
                    zkName = "未制卡";
                }
                label=new Label(10,i + 1, zkName, colmStyle);
                sheet.addCell(label);
                if (staff.getMessage() == null) {
                    label=new Label(11,i + 1, "", colmStyle);
                    sheet.addCell(label);
                }else {
                    label=new Label(11,i + 1, staff.getMessage(), colmStyle);
                    sheet.addCell(label);
                }
            }
            setColumnAutoSize2(sheet,10);
        }

        workbook.write();
        workbook.close();
    }

    public static void exportExcelByProblemData(String fileName, String sheetName, List resultList) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工编号");
        titles.add("姓名");
        titles.add("卡号");
        titles.add("性别");
        titles.add("联系方式");
        titles.add("家庭住址");
        titles.add("出生日期");
        titles.add("职业");
        titles.add("入职时间");
        titles.add("是否制卡");
        titles.add("所属物业");
        titles.add("所属企业");
        titles.add("备注");

        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            StaffVO staff = (StaffVO) resultList.get(i - 1);
            label=new Label(0,i, String.valueOf(i),colmStyle);
            sheet.addCell(label);
            label=new Label(1,i, staff.getBh(), colmStyle);
            sheet.addCell(label);
            label=new Label(2,i, staff.getName(),colmStyle);
            sheet.addCell(label);
            label=new Label(3,i, staff.getCardNo(),colmStyle);
            sheet.addCell(label);
            String sex = "";
            if (staff.getSex() == 1) {
                sex = "男";
            }else {
                sex = "女";
            }
            label=new Label(4,i, sex, colmStyle);
            sheet.addCell(label);
            label=new Label(5,i, staff.getPhone(), colmStyle);
            sheet.addCell(label);
            label=new Label(6,i, staff.getAddress(), colmStyle);
            sheet.addCell(label);
            label=new Label(7,i, staff.getCsTime(), colmStyle);
            sheet.addCell(label);
            label=new Label(8,i, staff.getZhiye(), colmStyle);
            sheet.addCell(label);
            String rzTime = "";
            if (null != staff.getRztime()) {
                rzTime = sdf.format(staff.getRztime());
            }
            label=new Label(9,i, rzTime, colmStyle);
            sheet.addCell(label);
            String zkName = "";
            if (null != staff.getZkFlag() && staff.getZkFlag() == 1) {
                zkName = "已制卡";
            }else{
                zkName = "未制卡";
            }
            label=new Label(10,i, zkName, colmStyle);
            sheet.addCell(label);
            label=new Label(11,i, staff.getWyName(), colmStyle);
            sheet.addCell(label);
            label=new Label(12,i, staff.getQyName(), colmStyle);
            sheet.addCell(label);
            label=new Label(13,i, staff.getMessage(), colmStyle);
            sheet.addCell(label);
        }
        setColumnAutoSize2(sheet,10);

        workbook.write();
        workbook.close();
    }

    /**
     * 导出黑白名单列表
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
    public static void exportStaffWhiteList(String fileName, String sheetName, List resultList) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工编号");
        titles.add("姓名");
        titles.add("国籍");
        titles.add("所属物业");
        titles.add("所属企业");
        titles.add("职业");
        titles.add("性别");
        titles.add("状态");
        titles.add("名单类型");
        titles.add("名单健康码颜色");
        titles.add("真实健康码颜色");
        titles.add("有效天数");

        Label label=null;


        int iMaxLines = 60000;
        int totalLines = resultList.size();
        int page = totalLines / iMaxLines;
        int lastLines = totalLines % iMaxLines;//尾页要写的行数
        if (totalLines == 0) {
            return;
        }
        if (page >= 1 && lastLines > 0) {
            page = page + 1;
        }
        if(page == 0){
            page = 1;
        }

        for(int p = 0; p < page; p ++){
            WritableSheet sheet = workbook.createSheet(sheetName + (p + 1), p);
            int iStartRow = 0;
            int iEndRow = 0;
            if (p == 0) {
                iStartRow = 0;
                if (page == 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            } else {
                iStartRow = p * iMaxLines;
                if (p == page - 1) {
                    iEndRow = totalLines - 1;
                } else {
                    iEndRow = (p + 1) * iMaxLines - 1;
                }
            }
            List listPageValue = new ArrayList();//保存每页的数据
            for (int i = iStartRow; i <= iEndRow; i++) {
                if (resultList.get(i) == null) {
                    continue;
                }
                listPageValue.add(resultList.get(i));
            }

            WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
            titleStyle.setBackground(Colour.SEA_GREEN);
            titleStyle.setAlignment(Alignment.CENTRE);
            titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < titles.size(); i ++){
                //x,y,第一行的列名
                label=new Label(i,0,titles.get(i),titleStyle);
                //7：添加单元格
                sheet.addCell(label);
            }

            WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
            colmStyle.setAlignment(Alignment.CENTRE);
            colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
            colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
            for(int i = 0; i < listPageValue.size(); i ++){
                StaffVO staff = (StaffVO) listPageValue.get(i);
                label=new Label(0,i + 1, String.valueOf(i + 1),colmStyle);
                sheet.addCell(label);
                label=new Label(1,i + 1, staff.getBh(), colmStyle);
                sheet.addCell(label);
                label=new Label(2,i + 1, staff.getName(),colmStyle);
                sheet.addCell(label);
                label=new Label(3,i + 1, staff.getGuoji(),colmStyle);
                sheet.addCell(label);
                label=new Label(4,i + 1, staff.getWyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(5,i + 1, staff.getQyName(), colmStyle);
                sheet.addCell(label);
                label=new Label(6,i + 1, staff.getZhiye(), colmStyle);
                sheet.addCell(label);
                String sex = "";
                if (staff.getSex() == 1) {
                    sex = "男";
                }else {
                    sex = "女";
                }
                label=new Label(7,i + 1, sex, colmStyle);
                sheet.addCell(label);

                String status = "";
                if (staff.getStatus() == 1) {
                    status = "在职";
                }else {
                    status = "离职";
                }
                label=new Label(8,i + 1, status, colmStyle);
                sheet.addCell(label);

                String whiteList = "";
                if (staff.getWhiteList() == 0) {
                    whiteList = "白名单";
                }else {
                    whiteList = "黑名单";
                }
                label=new Label(9,i + 1, whiteList, colmStyle);
                sheet.addCell(label);

                String qrCode = "";
                if ("red".equals(staff.getQrcode())) {
                    qrCode = "红码";
                }else if ("green".equals(staff.getQrcode())){
                    qrCode = "绿码";
                }else if ("orange".equals(staff.getQrcode())){
                    qrCode = "橙码";
                }
                label=new Label(10,i + 1, qrCode, colmStyle);
                sheet.addCell(label);

                String qrCodes = "";
//                String qrCode2 = businessService.getQrCode(staff.getId());
//                if ("red".equals(qrCode2)) {
//                    qrCodes = "红码";
//                }else if ("green".equals(qrCode2)){
                    qrCodes = "绿码";
//                }else if ("orange".equals(qrCode2)){
//                    qrCodes = "橙码";
//                }
                label=new Label(11,i + 1, qrCodes, colmStyle);
                sheet.addCell(label);

                if (null == staff.getQrCodeExpDay()) {
                    label=new Label(12,i + 1, "", colmStyle);
                    sheet.addCell(label);
                }else {
                    label=new Label(12,i + 1, staff.getQrCodeExpDay().toString(), colmStyle);
                    sheet.addCell(label);
                }
            }
            setColumnAutoSize2(sheet,10);
        }

        workbook.write();
        workbook.close();
    }

    public static void errorPictureCopy(String fileName, String sheetName, List resultList) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("员工编号");
        titles.add("姓名");
        titles.add("卡号");
        titles.add("头像");
        titles.add("性别");
        titles.add("联系方式");
        titles.add("家庭住址");
        titles.add("出生日期");
        titles.add("职业");
        titles.add("入职时间");
        titles.add("是否制卡");
        titles.add("备注");

        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            Staff staff = (Staff) resultList.get(i - 1);
            label=new Label(0,i, String.valueOf(i),colmStyle);
            sheet.addCell(label);
            label=new Label(1,i, staff.getBh(), colmStyle);
            sheet.addCell(label);
            label=new Label(2,i, staff.getName(),colmStyle);
            sheet.addCell(label);
            label=new Label(3,i, staff.getCardNo(),colmStyle);
            sheet.addCell(label);
            label=new Label(4,i, staff.getPicture(),colmStyle);
            sheet.addCell(label);
            String sex = "";
            if (staff.getSex() == 1) {
                sex = "男";
            }else {
                sex = "女";
            }
            label=new Label(5,i, sex, colmStyle);
            sheet.addCell(label);
            label=new Label(6,i, staff.getPhone(), colmStyle);
            sheet.addCell(label);
            label=new Label(7,i, staff.getAddress(), colmStyle);
            sheet.addCell(label);
            label=new Label(8,i, staff.getCstime(), colmStyle);
            sheet.addCell(label);
            label=new Label(9,i, staff.getZhiye(), colmStyle);
            sheet.addCell(label);
            String rzTime = "";
            if (null != staff.getRztime()) {
                rzTime = sdf.format(staff.getRztime());
            }
            label=new Label(10,i, rzTime, colmStyle);
            sheet.addCell(label);
            String zkName = "";
            if (null != staff.getZkFlag() && staff.getZkFlag() == 1) {
                zkName = "已制卡";
            }else{
                zkName = "未制卡";
            }
            label=new Label(11,i, zkName, colmStyle);
            sheet.addCell(label);
            label=new Label(12,i, staff.getMessage(), colmStyle);
            sheet.addCell(label);
        }
        setColumnAutoSize2(sheet,10);

        workbook.write();
        workbook.close();
    }

    public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
        // 获取本列的最宽单元格的宽度
        for (int i = 0; i < ws.getColumns(); i++) {
            int colWith = 0;
            for (int j = 0; j < ws.getRows(); j++) {
                String content = ws.getCell(i, j).getContents().toString();
                // int cellWith = content.length();
                byte[] bstrLength = content.getBytes();
                int cellWith = bstrLength.length;
                if (colWith < cellWith) {
                    colWith = cellWith;
                }
            }
            // 设置单元格的宽度为最宽宽度+额外宽度
            ws.setColumnView(i, colWith + extraWith);
        }
    }

    private static void setColumnAutoSize2(WritableSheet ws, int extraWith) {
        // 获取本列的最宽单元格的宽度
        for (int i = 0; i < ws.getColumns(); i++) {
            int colWith = 0;
            for (int j = 0; j < ws.getRows(); j++) {
                String content = ws.getCell(i, j).getContents().toString();
                // int cellWith = content.length();
                byte[] bstrLength = content.getBytes();
                int cellWith = bstrLength.length;
                if (colWith < cellWith) {
                    colWith = cellWith;
                }
            }
            // 设置单元格的宽度为最宽宽度+额外宽度
            ws.setColumnView(i, colWith);
        }
    }


    /**
     * 导入Excel
     * @param file
     * @return
     */
    public static Map<String,String> importExcelByBusiness(String fileName, MultipartFile file,List<Business> businessList) {
        Map<String, String> resultMap = new HashMap<>();
        List<String> bhList = new ArrayList<>();
        try {
            //格式化日期
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            int j = 0;
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }
                Business business = new Business();
                int i = 1;
                //第一个是列数，第二个是行数
                String num = "";
                if (rs.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
                    Double num1 = rs.getCell(0).getNumericCellValue(); //默认最左边编号也算一列 所以这里得j++
                    Integer num2 = num1.intValue();
                    num = String.valueOf(num2);
                }else if(rs.getCell(0).getCellTypeEnum() == CellType.FORMULA){
                    FormulaEvaluator hssfCell = rwb.getCreationHelper().createFormulaEvaluator();
                    CellValue tempCellValue = hssfCell.evaluate(rs.getCell(0));
                    Double num2 = tempCellValue.getNumberValue();
                    num = String.valueOf(num2);
                }else {
                    num = rs.getCell(0).getStringCellValue();
                }

                String name = null;
                if (rs.getCell(1) != null && rs.getCell(1).getCellTypeEnum() != null) {
                    if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
                        Double name1 = rs.getCell(1).getNumericCellValue();
                        Integer name2 = name1.intValue();
                        name = String.valueOf(name2);
                    }else {
                        name = rs.getCell(1).getStringCellValue();
                    }
                }else {
                    resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>商铺名称不能为空</span>");
                    return resultMap;
                }

                if (ValidateHelper.isEmptyString(name)) {
                    resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>商铺名称不能为空</span>");
                    return resultMap;
                }

                int nameCount = businessService.selectBusinessName(name);
                if (nameCount > 0) {
                    resultMap.put("error","序号：" + num + "<br>商铺名称：" + "<span style='color:red;'>" + name + "</span>" + "<br>错误信息：<span style='color:red;'>商铺名称已存在</span>");
                    return resultMap;
                }
                String address = null;
                if (rs.getCell(2) != null && rs.getCell(2).getCellTypeEnum() != null) {
                    if (rs.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
                        Double address1 = rs.getCell(2).getNumericCellValue();
                        Integer address2 = address1.intValue();
                        address = String.valueOf(address2);
                    }else {
                        address = rs.getCell(2).getStringCellValue();
                    }
                }


                String fzr = null;
                if (rs.getCell(3) != null && rs.getCell(3).getCellTypeEnum() != null) {
                    if (rs.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
                        Double fzr1 = rs.getCell(3).getNumericCellValue();
                        Integer fzr2 = fzr1.intValue();
                        fzr = String.valueOf(fzr2);
                    }else {
                        fzr = rs.getCell(3).getStringCellValue();
                    }
                }


                String wyName = null;
                if (rs.getCell(4) != null && rs.getCell(4).getCellTypeEnum() != null) {
                    if (rs.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {
                        Double wuye1 = rs.getCell(4).getNumericCellValue();
                        Integer wuye2 = wuye1.intValue();
                        wyName = String.valueOf(wuye2);
                    }else {
                        wyName = rs.getCell(4).getStringCellValue();
                    }
                }


                String message = null;
                if (rs.getCell(5) != null && rs.getCell(5).getCellTypeEnum() != null) {
                    if (rs.getCell(5).getCellTypeEnum() == CellType.NUMERIC) {
                        Double m1 = rs.getCell(5).getNumericCellValue();
                        Integer m2 = m1.intValue();
                        message = String.valueOf(m2);
                    }else {
                        message = rs.getCell(5).getStringCellValue();
                    }
                }

                if (ValidateHelper.isEmptyString(wyName)) {
                    resultMap.put("error","序号：" + num + "<br>商铺名称：" + name + "<br>错误信息：<span style='color:red;'>商户所属物业不能为空</span>");
                    return resultMap;
                }

                Wuye wuye = wuyeService.selectByName(wyName);
                if (wuye == null) {
                    resultMap.put("error","序号：" + num + "<br>商铺名称：" + name + "<br>物业：" + "<span style='color:red;'>" + wyName + "</span>"  + "<br>错误信息：<span style='color:red;'>" + wyName + "不存在</span>");
                    return resultMap;
                }
                business.setWyid(wuye.getId());
                j ++;
                int maxBh = businessService.getMaxBh() + j;
                Integer busiCode = generateBusiCode(maxBh);

                name = name.replaceAll("\\n","");
                if (ValidateHelper.isNotEmptyString(message)) {
                    message = message.replaceAll("<", "[");
                    message = message.replaceAll(">", "]");
                    business.setMessage(message);
                }
                business.setName(name);
                business.setStatus(1);
                business.setPassword(PasswordUtil.jiami("123456"));
                business.setBh(busiCode);
                business.setFzr(fzr);
                business.setCreateTime(new Date());
                business.setAddress(address);
                businessList.add(business);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        resultMap.put("sucess","sucess");
        return resultMap;
    }


    public static Integer generateBusiCode(Integer bh) {
        Business business = businessService.selectBusinessByBh(bh);
        if (business != null) {
            return generateBusiCode(bh + 1);
        }else {
            return bh;
        }
    }

    public static String generateBh(String bh,String sex,List<String> bhList) {
        String tmpBh = bh.substring(0,bh.length()-1);
        String str = bh.substring(bh.length() - 1);
        char bh2 = bh.charAt(bh.length()-1);
        int num = (int) bh2;
        if (num > 47 && num < 58) {
            bh2 = (char) (bh2 - '0');
        }

        int i = 0;
        boolean addFlag = false;
        if (bhList.size() > 0) {
            for (String bhStr : bhList) {
                if (bhStr.equals(bh)) {
                    addFlag = true;
                    break;
                }
            }
        }
        if (addFlag) {
            if (sex.equals("男")) {
                if (bh2 > 10) {
                    i = bh2 + 1;
                    tmpBh = tmpBh + (char)i;
                }else {
                    i = Integer.valueOf(bh2);
                    i = i + 2;
                    if (i > 10) {
                        i = 'X' + (i - 11) / 2;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        tmpBh = tmpBh + i;
                    }
                }
            }else {
                if (bh2 >= 10) {
                    i = bh2 + 1;
                    tmpBh = tmpBh + (char)i;
                }else {
                    i = Integer.valueOf(bh2);
                    i = i + 2;
                    if (i > 10) {
                        i = 'A' + (i - 10) / 2;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        tmpBh = tmpBh + i;
                    }
                }
            }
            return generateBh(tmpBh,sex,bhList);
        }
        List<Staff> staffList = staffService.getStaffInfoByBh(bh);
        List<StaffHistory> staffHistoryList = staffHistoryService.getStaffHisByBh(bh);
        if (staffList.size() > 0) {
            if (sex.equals("男")) {
                if (bh2 > 10) {
                    i = bh2 + 1;
                    tmpBh = tmpBh + (char)i;
                }else {
                    i = Integer.valueOf(bh2);
                    i = i + 2;
                    if (i > 10) {
                        i = 'X' + (i - 11) / 2;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        tmpBh = tmpBh + i;
                    }
                }
            }else {
                if (bh2 > 10) {
                    i = bh2 + 1;
                    tmpBh = tmpBh + (char)i;
                }else {
                    i = Integer.valueOf(bh2);
                    i = i + 2;
                    if (i > 10) {
                        i = 'A' + (i - 10) / 2;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        tmpBh = tmpBh + i;
                    }
                }
            }
            return generateBh(tmpBh,sex,bhList);
        }else {
            if (staffHistoryList.size() > 0) {
                if (sex.equals("男")) {
                    if (bh2 > 10) {
                        i = bh2 + 1;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        i = Integer.valueOf(bh2);
                        i = i + 2;
                        if (i > 10) {
                            i = 'X' + (i - 11) / 2;
                            tmpBh = tmpBh + (char)i;
                        }else {
                            tmpBh = tmpBh + i;
                        }

                    }
                }else {
                    if (bh2 > 10) {
                        i = bh2 + 1;
                        tmpBh = tmpBh + (char)i;
                    }else {
                        i = Integer.valueOf(bh2);
                        i = i + 2;
                        if (i > 10) {
                            i = 'A' + (i - 10) / 2;
                            tmpBh = tmpBh + (char)i;
                        }else {
                            tmpBh = tmpBh + i;
                        }
                    }
                }
                return generateBh(tmpBh,sex,bhList);
            }else {
                return bh;
            }
        }
    }

    /**
     * 导入未检测核酸Excel
     * @param file
     * @return
     */
    public static boolean importExcelByHsJc(String fileName, MultipartFile file,List<String> bhList) {

        try {
            //格式化日期
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }
                Staff staff = new Staff();
                int i = 1;
                //第一个是列数，第二个是行数
                String num = rs.getCell(0).getStringCellValue();
                bhList.add(num);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 批量导入删除员工
     * @param file
     * @return
     */
    public static boolean importDeleteStaff(String fileName, MultipartFile file,Map<String, String> ids) {
        try {
            //格式化日期
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }
                int i = 1;
                //第一个是列数，第二个是行数
                String bh = rs.getCell(0).getStringCellValue();
                bh = StringUtils.deleteWhitespace(bh);

                String remark = "";
                if (null != rs.getCell(1)) {
                    if(rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC){
                        Double remark1 = rs.getCell(1).getNumericCellValue();
                        Integer remark2 = remark1.intValue();
                        remark = String.valueOf(remark2);
                    }else {
                        remark = rs.getCell(1).getStringCellValue();
                    }
                }

                if (ValidateHelper.isNotEmptyString(remark)) {
                    ids.put(bh, remark);
                }else {
                    ids.put(bh, "");
                }

            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }

    /**
     * 导入Excel
     * @param file
     * @return
     */
    public static Map<String,String> importExcel(String fileName, MultipartFile file, List<Staff> staffList, List<Integer> wycodeList, Admin admin) {
        Map<String, String> resultMap = new HashMap<>();
        List<String> bhList = new ArrayList<>();
        try {
            //格式化日期
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }
                Staff staff = new Staff();
                int i = 1;
                //第一个是列数，第二个是行数
                if (rs.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
                    Double num = rs.getCell(0).getNumericCellValue(); //默认最左边编号也算一列 所以这里得j++
                }else if(rs.getCell(0).getCellTypeEnum() == CellType.FORMULA){
                    FormulaEvaluator hssfCell = rwb.getCreationHelper().createFormulaEvaluator();
                    CellValue tempCellValue = hssfCell.evaluate(rs.getCell(0));
                    Double num = tempCellValue.getNumberValue();
                }else {
                    String num = rs.getCell(0).getStringCellValue();
                }
                String zhiye = rs.getCell(1).getStringCellValue();
                String wyName = "";
                if (rs.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
                    Double wyName1 = rs.getCell(2).getNumericCellValue();
                    Integer wyName2 = wyName1.intValue();
                    wyName = String.valueOf(wyName2);
                }else {
                    wyName = rs.getCell(2).getStringCellValue();
                }
                String gsName = "";
                if (rs.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
                    Double gsName1 = rs.getCell(3).getNumericCellValue();
                    Integer gsName2 = gsName1.intValue();
                    gsName = String.valueOf(gsName2);
                }else {
                    gsName = rs.getCell(3).getStringCellValue();
                }
                String address = "";
                if (rs.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {
                    Double address1 = rs.getCell(4).getNumericCellValue();
                    Integer address2 = address1.intValue();
                    address = String.valueOf(address2);
                }else {
                    address = rs.getCell(4).getStringCellValue();
                }

                if (StringUtil.isNull(zhiye) || StringUtil.isNull(wyName)) {
                    continue;
                }

                String name = "";
                try {
                    name = rs.getCell(5).getStringCellValue();
                }catch (Exception e) {
                    try {
                        Double d  = rs.getCell(5).getNumericCellValue();
                        Integer it = d.intValue();
                        name = String.valueOf(it);
                    }catch (Exception e1) {
                    }finally {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + "<span style='color:red;'>" + name + "</span>" + "<br><span style='color:red;'>错误信息：姓名不能是纯数字</span>");
                        return resultMap;
                    }
                }

                String guoji = "";
                if (rs.getCell(6).getCellTypeEnum() == CellType.NUMERIC) {
                    Double guoji1 = rs.getCell(6).getNumericCellValue();
                    Integer guoji2 = guoji1.intValue();
                    guoji = String.valueOf(guoji2);
                }else {
                    guoji = rs.getCell(6).getStringCellValue();
                }

                String sex = "";
                try {
                    sex = rs.getCell(7).getStringCellValue();
                }catch (Exception e) {
                    resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名："  + name  + "<br>性别：" + "<span style='color:red;'>" + sex + "</span>" + "<br><span style='color:red;'>错误信息：性别不能是纯数字</span>");
                    return resultMap;
                }

                String cstime1 = "";
                try {
                    if (rs.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
                        Date cstimeTmp = rs.getCell(8).getDateCellValue();
                        if (cstimeTmp != null) {
                            cstime1 = simpleDateFormat.format(cstimeTmp);
                        }
                    }else {
                        cstime1 = rs.getCell(8).getStringCellValue();
                    }
                }catch (Exception e) {
                    resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" + "<span style='color:red;'>" +  cstime1 + "</span>" + "<br><span style='color:red;'>错误信息：出生日期格式不正确</span>");
                    return resultMap;
                }

                //备注
                String message = "";
                if (rs.getCell(9)!=null){  // fix by lgh
                    if (rs.getCell(9).getCellTypeEnum() == CellType.NUMERIC) {
                        Double message1 = rs.getCell(9).getNumericCellValue();
                        Integer message2 = message1.intValue();
                        message = String.valueOf(message2);
                    }else {
                        message = rs.getCell(9).getStringCellValue();
                    }
                }

                // 证件号码
                String sfz = "";
                if (rs.getCell(10)!=null){
                    if (rs.getCell(10).getCellTypeEnum() == CellType.NUMERIC) {
                        Double sfz1 = rs.getCell(10).getNumericCellValue();
                        Integer sfz2 = sfz1.intValue();
                        sfz = String.valueOf(sfz2);
                    }else {
                        sfz = rs.getCell(10).getStringCellValue();
                    }
                }

                if (ValidateHelper.isNotEmptyString(sfz)) {
                    StaffHistory staffHistory = staffHistoryService.getStaffHistoryBySfz(sfz);
                    if (null != staffHistory) {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>证件号码：" + "<span style='color:red;'>" +  sfz + "</span>" + "<br><span style='color:red;'>错误信息：该员工在员工回收站中</span>");
                        return resultMap;
                    }
                }

                if (cstime1.contains("-")) {
                    cstime1 = cstime1.replaceAll("-","/");
                }

                Pattern p1 = Pattern.compile("[0-9]*");
                Matcher isNum = p1.matcher(zhiye.charAt(zhiye.length()-1)+"");
                if (isNum.matches()) {
                    zhiye = zhiye.substring(0,zhiye.length() - 1);
                }
                if (zhiye.contains(",")) {
                    zhiye = zhiye.replaceAll(",","，");
                }else if(zhiye.contains("、")){
                    zhiye = zhiye.replaceAll("、","，");
                }
                Dictionary dic = dictionaryService.getDicByName("Zy",zhiye);
                if (null == dic) {
                    resultMap.put("error","职业：" + "<span style='color:red;'>" + zhiye + "</span>" + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br><span style='color:red;'>错误信息：职业不存在</span>");
                    return resultMap;
                }

                String cstime = cstime1;
                cstime = StringUtils.deleteWhitespace(cstime);
                if (StringUtil.isNull(cstime)) {
                    cstime = "0000/00/00";
                    staff.setCstime(cstime);
                }else {
                    staff.setCstime(cstime);
                }
                int five = 0;

                // 去空格和换行符处理
                zhiye = StringUtils.deleteWhitespace(zhiye);
                wyName = StringUtils.deleteWhitespace(wyName);
                gsName = StringUtils.deleteWhitespace(gsName);

                Wuye wuye = wuyeService.selectByName(wyName);
                Qiye qiye = qiyeService.getQiyeByName(gsName);

                if (null == wuye) {
                    resultMap.put("error","职业：" + zhiye + "<br>物业：" + "<span style='color:red;'>" + wyName + "</span>" + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br>错误信息：<span style='color:red;'>" + wyName + "，物业不存在</span>");
                    return resultMap;
                }

                if (wuye != null && qiye != null) {
                    Wuye wy = wuyeService.selectID(qiye.getWyID());
                    if (wy != null && !wy.getWyname().equals(wyName)) {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + "<span style='color:red;'>" + wyName + "</span>" + "<br>公司：" + "<span style='color:red;'>" + gsName + "</span>"  +"<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br>错误信息：<span style='color:red;'>" + "'" +gsName + "'" + "已存在于" + "'" + wy.getWyname() + "'" + "物业公司，请修改公司名称</span>");
                        return resultMap;
                    }
                }else if (qiye != null && wuye == null) {
                    Wuye wy = wuyeService.selectID(qiye.getWyID());
                    resultMap.put("error","职业：" + zhiye + "<br>物业：" + "<span style='color:red;'>" + wyName + "</span>" + "<br>公司：" + "<span style='color:red;'>" + gsName + "</span>"  +"<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br>错误信息：<span style='color:red;'>" + "'" +gsName + "'" + "已存在于" + "'" + wy.getWyname() + "'" + "物业公司，请修改公司名称</span>");
                    return resultMap;
                }

                // 获取物业编码最后两位
                String two = "";
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

                if (null == wuye) {
                    five = 6;
                    Wuye saveWy = new Wuye();
                    Wuye minCode  = wuyeService.getOne(new QueryWrapper<Wuye>().orderByAsc("wycode").last("limit 1"));
                    Integer code = Integer.valueOf(minCode.getWycode()) - 1;
                    if (wycodeList.contains(code)) {
                        Integer code2 = wycodeList.stream().reduce(Integer::min).get();
                        code = code2 - 1;
                    }
                    code = queryWuCode(code);
                    if (code < 10) {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br><span style='color:red;'>错误信息：物业编号已重复</span>");
                        return resultMap;
                    }

                    wycodeList.add(code);
                    String tmpCode = String.valueOf(code);
                    if (tmpCode.length() >= 2) {
                        two = tmpCode.substring(tmpCode.length() - 2);
                    }else {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" +  cstime1  + "<br><span style='color:red;'>错误信息：物业编码少于2位，无法生成员工编号</span>");
                        return resultMap;
                    }

                    saveWy.setWyname(wyName);
                    saveWy.setStatus(1);
                    saveWy.setCreatetime(sdf.format(new Date()));
                    saveWy.setAddFlag(0);
                    saveWy.setPassword(PasswordUtil.jiami("123456"));
                    saveWy.setWycode(code);
                    if (null != admin) {
                        saveWy.setOperUser(admin.getId());
                    }
                    if(wuyeService.save(saveWy)){
                        if(qiye == null){
                            Qiye saveQiye = new Qiye();
                            saveQiye.setQyname(gsName);
                            saveQiye.setShstatus(1);
                            saveQiye.setAddress(address);
                            saveQiye.setStatus(1);
                            saveQiye.setWyID(saveWy.getId());
                            saveQiye.setCreateTime(new Date());
                            if (null != admin) {
                                saveQiye.setOperUser(admin.getId());
                            }
                            if(qiyeService.save(saveQiye)){
                                staff.setQyID(saveQiye.getId());
                            }
                        }else {
                            staff.setQyID(qiye.getId());
                        }
                    }
                }else {
                    if(qiye == null){
                        Qiye saveQiye = new Qiye();
                        saveQiye.setQyname(gsName);
                        saveQiye.setShstatus(1);
                        saveQiye.setAddress(address);
                        saveQiye.setStatus(1);
                        saveQiye.setWyID(wuye.getId());
                        saveQiye.setCreateTime(new Date());
                        if(qiyeService.save(saveQiye)){
                            staff.setQyID(saveQiye.getId());
                        }
                    }else {
                        staff.setQyID(qiye.getId());
                    }
                    if (wuye.getAddFlag() == 0) {
                        five = 6;
                    }else {
                        five = 1;
                    }
                    String tmpCode = String.valueOf(wuye.getWycode());
                    two = tmpCode.substring(tmpCode.length() - 2);
                }
                name = name.replaceAll("\\n","");
                staff.setName(name);
                staff.setGuoji(guoji);
                if (sex.equals("男")) {
                    staff.setSex(1);
                }
                else {
                    staff.setSex(0);
                }
                String str[] = null;
                if (cstime.contains(".")) {
                    str = cstime.split(".");
                }else if (cstime.contains("-")) {
                    str = cstime.split("-");
                }else {
                    str = cstime.split("/");
                }

                String monthStr = "";
                String dayStr = "";
                Calendar calendar = Calendar.getInstance();
                try {
                    calendar.setTime(simpleDateFormat.parse(cstime));
                }catch (Exception e){
                    resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + name +  "<br>出生日期：" + "<span style='color:red;'>" +  cstime + "</span>" + "<br><span style='color:red;'>错误信息：出生日期格式不正确</span>");
                    return resultMap;
                }

                int month = calendar.get(Calendar.MONTH) + 1;
                int day = calendar.get(Calendar.DAY_OF_MONTH);
                // 获取出生年月，没有2位数则自动补0
                if (str[1].startsWith("0")) {
                    monthStr = str[1];
                } else {
                    monthStr = month >= 10 ? String.valueOf(month) : "0" + month;
                }
                if (str[2].startsWith("0")) {
                    dayStr = str[2];
                }else {
                    dayStr = day >= 10 ? String.valueOf(day) : "0" + day;
                }
                // 去数字
                String strNum = name.replaceAll("\\d+","");
                // 去空格
                String strName1 = StringUtils.deleteWhitespace(strNum);
                String regEx="[\n`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，·、？]";
                Pattern pt = Pattern.compile(regEx);
                Matcher mc = pt.matcher(strName1);
                // 去掉名字中的特殊字符
                String newName = mc.replaceAll("").trim();
                String tmpName = newName;

                Pattern p = Pattern.compile("[\u4e00-\u9fa5]+");
                Matcher m = p.matcher(newName);
                String one = "";
                // 判断是否有中文
                if (m.find()) {
                    Pattern pattern = Pattern.compile("[^\u4E00-\u9FA5]");
                    Matcher matcher = pattern.matcher(newName);
                    // 获取所有中文
                    newName = matcher.replaceAll("");
                    newName = StringUtils.deleteWhitespace(newName);
                    // 中文个数
                    if (newName.length() > 1) {
                        // 截最后2位生成拼音首字母
                        newName = newName.substring(newName.length() - 2);
                        //newName = ChineseToEnglishUtil.getPinYinHeadChar(newName);
                        newName = PinYinJPUtil.changeToGetShortPinYin(newName);
                    }else if (newName.length() == 1) {
                        // 取一个汉字拼音的前2位
                        //newName = ChineseToEnglishUtil.getPingYin(newName);
                        newName = PinYinJPUtil.changeToTonePinYin(newName);
                        if (newName.length() == 1 && tmpName.length() > 1) {
                            Matcher m2 = p.matcher(tmpName);
                            if (m2.find()) {
                                Pattern pattern2 = Pattern.compile("[^\u4E00-\u9FA5]");
                                Matcher matcher2 = pattern2.matcher(tmpName);
                                // 获取所有中文
                                String tmpName1 = matcher2.replaceAll("");
                                String tmpName2 = StringUtils.deleteWhitespace(tmpName1);
                                // 获取中文之后的字符串
                                String str3 = tmpName.substring(0, tmpName.indexOf(tmpName2));
                                String str4 = tmpName.substring(str3.length()+1, tmpName.length());
                                Pattern pt2 = Pattern.compile(regEx);
                                Matcher mc2 = pt2.matcher(str3);
                                // 去掉中文前面特殊字符
                                String str6 = mc2.replaceAll("").trim();

                                Pattern pt3 = Pattern.compile(regEx);
                                Matcher mc3 = pt3.matcher(str4);
                                // 去掉中文前面特殊字符
                                String str7 = mc3.replaceAll("").trim();
                                //判断前面是否有两个字符
                                if (str6.length() >= 2) {
                                    newName = str6.substring(0,2);
                                }else {
                                    if(str7.length() > 1) {
                                        newName = newName + str7.substring(0,1);  // lgh ------------------------
                                    } else if (str7.length() == 1) {
                                        newName = newName + str7;
                                    }else {
                                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + "<span style='color:red;'>" + newName + "</span>" + "<br><span style='color:red;'>错误信息：姓名不符合规范</span>");
                                        return resultMap;
                                    }
                                }
                            }
                        }else if (newName.length() == 1) {// 处理"啊"，"哦"，"额"单音
                            newName = newName + newName;
                        }else {
                            if (newName!=null && newName.length()>2)
                            {
                                newName = newName.substring(0,2);
                            }
                        }
                    }
                    // 中文名字转拼音首字母大写
                    one = newName.toUpperCase();
                }else if(Pattern.compile("[a-zA-Z]").matcher(newName).find()){
                    newName = newName.replaceAll("[^a-zA-Z]","");
                    if (newName.length() > 1) {
                        newName = newName.substring(0,2);
                    }else if (newName.length() == 1){
                        newName = newName + newName;
                    }else {
                        resultMap.put("error","职业：" + zhiye + "<br>物业：" + wyName + "<br>公司：" + gsName + "<br>姓名：" + "<span style='color:red;'>" + newName + "</span>" + "<br><span style='color:red;'>错误信息：姓名中汉字或字母长度必须大于1</span>");
                        return resultMap;
                    }
                    one = newName.toUpperCase();
                }else if (Pattern.compile("[\u1000-\u109F]").matcher(newName).find()) {
                    one = "MD";
                }else if (Pattern.compile("[\u0E80-\u0EFF]").matcher(newName).find()) {
                    one = "LW";
                }else if (Pattern.compile("[\u0E00-\u0E7F]").matcher(newName).find()) {
                    one = "TG";
                }


                String tree = dic.getCode();
                String four = monthStr + dayStr;
                String monthStr2 = "";
                if (monthStr.equals("10")) {
                    monthStr2 = "0" + monthStr;
                }else {
                    monthStr2 = monthStr;
                }
                String dayStr2 = "";
                if (dayStr.equals("10")) {
                    dayStr2 = "0" + dayStr;
                }else {
                    dayStr2 = dayStr;
                }
                String four2 = monthStr2 + dayStr2;
                int six = 0;
                if (staff.getSex() == 1) {
                    for (int j = 0; j < 10; j ++) {
                        if (j % 2 == 0) {
                            continue;
                        }
                        six = j;
                        break;
                    }
                }else {
                    for (int j = 0; j < 10; j ++) {
                        if (j % 2 == 0) {
                            six = j;
                            break;
                        }
                        continue;
                    }
                }
                String bh = one + two + tree + four + five + six;
                String bh2 = one + two + four2;
                String bh3 = generateBh(bh,sex,bhList);
                bhList.add(bh3);
                staff.setJfstatus(0);
                staff.setStatus(1);
                staff.setBh(bh3);
                staff.setBh2(bh2);
                staff.setRztime(new Date());
                staff.setShstatus(1);
                staff.setZhiye(zhiye);
                staff.setMessage(message);
                if (null != admin) {
                    staff.setOperUser(admin.getId());
                }
                staffList.add(staff);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            resultMap.put("error", "错误信息：<span style='color:red;'>您上传的excel格式不正确，请检查数据是否包含函数运算或数据格式是否正确</span>");
            return resultMap;
        }
        resultMap.put("sucess","sucess");
        return resultMap;
    }


    public static void main(String[] args) {
        System.out.println(Pattern.compile("[\u0E00-\u0E7F]").matcher("นางคำแดง ").find());
    }

    public static Integer queryWuCode(Integer code) {
        String codeStr = String.valueOf(code);
        List<Wuye> wyList = wuyeService.list();
        if (wyList.size() > 0) {
            for (Wuye wy : wyList) {
                String wyCode = String.valueOf(wy.getWycode());
                if (wyCode.length() >= 3) {
                    wyCode = wyCode.substring(1);
                    if (wyCode.equals(codeStr)) {
                        return queryWuCode(code - 1);
                    }
                }else {
                    if (wy.getWycode() == code) {
                        return queryWuCode(code - 1);
                    }
                }
            }
        }
        return code;
    }

    /**
     * 批量导入修改员工企业
     * @param file
     * @return
     */
    public static Map<String, String> importExcelByUpdateStaffQy(MultipartFile file) {
        Map<String, String> resultMap = new HashMap<>();
        try {
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }

                // 序号
                String num = "";
                if (rs.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
                    Double num1 = rs.getCell(0).getNumericCellValue();
                    Integer num2 = num1.intValue();
                    num = String.valueOf(num2);
                }else if(rs.getCell(0).getCellTypeEnum() == CellType.FORMULA){
                    FormulaEvaluator hssfCell = rwb.getCreationHelper().createFormulaEvaluator();
                    CellValue tempCellValue = hssfCell.evaluate(rs.getCell(0));
                    Double num1 = tempCellValue.getNumberValue();
                    Integer num2 = num1.intValue();
                    num = String.valueOf(num2);
                }else {
                    num = rs.getCell(0).getStringCellValue();
                }

                // 编号
                String bh = "";
                if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
                    Double bh1 = rs.getCell(1).getNumericCellValue();
                    Integer bh2 = bh1.intValue();
                    bh = String.valueOf(bh2);
                }else {
                    bh = rs.getCell(1).getStringCellValue();
                }

                // 新物业名称
                String newWy = "";
                if (rs.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
                    Double newWy1 = rs.getCell(2).getNumericCellValue();
                    Integer newWy2 = newWy1.intValue();
                    newWy = String.valueOf(newWy2);
                }else {
                    newWy = rs.getCell(2).getStringCellValue();
                }

                //新企业名称
                String newQy = "";
                if (rs.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
                    Double newQy1 = rs.getCell(3).getNumericCellValue();
                    Integer newQy2 = newQy1.intValue();
                    newQy = String.valueOf(newQy2);
                }else {
                    newQy = rs.getCell(3).getStringCellValue();
                }

                //新职业名称
                String newZy = "";
                System.out.println("职业："+rs.getCell(4));
                if (rs.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {
                    Double newZy1 = rs.getCell(4).getNumericCellValue();
                    Integer newZy2 = newZy1.intValue();
                    newZy = String.valueOf(newZy2);
                }else {
                    newZy = rs.getCell(4).getStringCellValue();
                }

                // 编号不能为空
                if (ValidateHelper.isEmptyString(bh)) {
                    resultMap.put("error","序号：" + num + "<br><span style='color:red;'>错误信息：编号不能为空</span>");
                    return resultMap;
                }

                Staff staff = staffService.getStaffByBh(bh);
                // 编号不存在
                if (null == staff) {
                    resultMap.put("error","序号：" + num + "<br>编号：" + "<span style='color:red;'>" + bh + "</span>" + "<br><span style='color:red;'>错误信息：编号不存在</span>");
                    return resultMap;
                }

                // 新物业名称不能为空
                if (ValidateHelper.isEmptyString(newWy)) {
                    resultMap.put("error","序号：" + num + "<br><span style='color:red;'>错误信息：新物业名称不能为空</span>");
                    return resultMap;
                }

                Wuye wuye = wuyeService.selectByName(newWy);
                // 新物业名称不存在
                if (null == wuye) {
                    resultMap.put("error","序号：" + num + "<br>新物业名称：" + "<span style='color:red;'>" + newWy + "</span>" + "<br><span style='color:red;'>错误信息：新物业名称不存在</span>");
                    return resultMap;
                }

                // 新企业名称不能为空
                if (ValidateHelper.isEmptyString(newQy)) {
                    resultMap.put("error","序号：" + num + "<br><span style='color:red;'>错误信息：新企业名称不能为空</span>");
                    return resultMap;
                }

                Qiye qiye = qiyeService.getQiyeByName(newQy);
                // 新企业名称不存在
                if (null == qiye) {
                    resultMap.put("error","序号：" + num + "<br>新企业名称：" + "<span style='color:red;'>" + newQy + "</span>" + "<br><span style='color:red;'>错误信息：新企业名称不存在</span>");
                    return resultMap;
                }

                Wuye wuyeByQy = wuyeService.selectID(qiye.getWyID());
                // 新企业所属物业不存在
                if (null == wuyeByQy) {
                    resultMap.put("error","序号：" + num + "<br>新企业名称：" + "<span style='color:red;'>" + newQy + "</span>" + "<br><span style='color:red;'>错误信息：新企业所属物业不存在</span>");
                    return resultMap;
                }

                // 新企业所属物业和新物业名称不一致
                if (!wuyeByQy.getWyname().equals(wuye.getWyname())) {
                    resultMap.put("error","序号：" + num + "<br>新企业名称：" + "<span style='color:red;'>" + newQy + "</span>" + "<br><span style='color:red;'>错误信息：新企业所属物业名称和新物业名称不一致</span>");
                    return resultMap;
                }

                String zy = StringUtils.deleteWhitespace(newZy);
                if (ValidateHelper.isNotEmptyString(zy)) {
                    Dictionary dictionary = dictionaryService.getDicByName("Zy", zy);
                    // 新职业名称不存在
                    if (null == dictionary) {
                        resultMap.put("error","序号：" + num + "<br>新职业名称：" + "<span style='color:red;'>" + newZy + "</span>" + "<br><span style='color:red;'>错误信息：新职业名称不存在</span>");
                        return resultMap;
                    }
                    staff.setZhiye(zy);
                }
                // 判断员工企业是否有变更
                if (!staff.getQyID().equals(qiye.getId())) {
                    staffQiyeChangeRecordService.saveStaffQyChangeRecord(staff.getQyID(), qiye.getId(),staff.getId());
                }

                staff.setQyID(qiye.getId());
                staffService.updateById(staff);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            resultMap.put("error", "错误信息：<span style='color:red;'>您上传的excel格式不正确，请检查数据是否包含函数运算或数据格式是否正确</span>");
            return resultMap;
        }
        resultMap.put("success","success");
        return resultMap;
    }

    /**
     * 导入Excel
     * @param file
     * @return
     */
    public static Map<String,String> importExcelByZzz(String fileName, MultipartFile file, Integer importType) {
        Map<String, String> resultMap = new HashMap<>();
        try {
            InputStream is = file.getInputStream();
            Workbook rwb = WorkbookFactory.create(is);
            Sheet sheet = rwb.getSheetAt(0);
            Date dueTime = DateUtil.offsetDay(new Date(),180);
            Integer effectiveMonth = 180 / 30;

            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
                if (rs == null){
                    continue;
                }
                //第一个是列数，第二个是行数
                String num = "";
                if (rs.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
                    Double num1 = rs.getCell(0).getNumericCellValue(); //默认最左边编号也算一列 所以这里得j++
                    Integer num2 = num1.intValue();
                    num = String.valueOf(num2);
                }else if(rs.getCell(0).getCellTypeEnum() == CellType.FORMULA){
                    FormulaEvaluator hssfCell = rwb.getCreationHelper().createFormulaEvaluator();
                    CellValue tempCellValue = hssfCell.evaluate(rs.getCell(0));
                    Double num2 = tempCellValue.getNumberValue();
                    num = String.valueOf(num2);
                }else {
                    num = rs.getCell(0).getStringCellValue();
                }

                if (importType == 1) {
                    String bh = null;
                    if (rs.getCell(1) != null && rs.getCell(1).getCellTypeEnum() != null) {
                        if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
                            Double bh1 = rs.getCell(1).getNumericCellValue();
                            Integer bh2 = bh1.intValue();
                            bh = String.valueOf(bh2);
                        }else {
                            bh = rs.getCell(1).getStringCellValue();
                        }
                    }else {
                        resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>编号不能为空</span>");
                        return resultMap;
                    }
                    bh = bh.replaceAll("\\n","");
                    Staff staff = staffService.getStaffByBh(bh);
                    if (null == staff) {
                        resultMap.put("error","序号：" + num + "<br>编号：" + "<span style='color:red;'>" + bh + "<br>错误信息：<span style='color:red;'>编号不存在</span>");
                        return resultMap;
                    }
                }else {
                    String sfz = null;
                    if (rs.getCell(1) != null && rs.getCell(1).getCellTypeEnum() != null) {
                        if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
                            DecimalFormat df = new DecimalFormat("#");
                            sfz = String.valueOf(df.format(rs.getCell(1).getNumericCellValue()));
                        }else {
                            sfz = rs.getCell(1).getStringCellValue();
                        }
                    }else {
                        resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>证件号不能为空</span>");
                        return resultMap;
                    }
                    sfz = sfz.replaceAll("\\n","");
                    Staff staff = staffService.getStaffInfoBySfz(sfz);
                    if (null == staff) {
                        resultMap.put("error","序号：" + num + "<br>证件号：" + "<span style='color:red;'>" + sfz + "<br>错误信息：<span style='color:red;'>证件号不存在</span>");
                        return resultMap;
                    }
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            resultMap.put("error", "错误信息：<span style='color:red;'>您上传的excel格式不正确，请检查数据是否包含函数运算或数据格式是否正确</span>");
            return resultMap;
        }
        resultMap.put("sucess","sucess");
        return resultMap;
    }

    /**
     * 导入Excel
     * @param file
     * @return
     */
//    public static Map<String,String> importExcelByWorkCard(String fileName, MultipartFile file, Integer importType) {
//        Map<String, String> resultMap = new HashMap<>();
//        try {
//            InputStream is = file.getInputStream();
//            Workbook rwb = WorkbookFactory.create(is);
//            Sheet sheet = rwb.getSheetAt(0);
//            Date dueTime = DateUtil.offsetDay(new Date(),180);
//            Integer effectiveMonth = 180 / 30;
//
//            for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据
//                Row rs = sheet.getRow(r);//通过sheet表单对象得到 行对象
//                if (rs == null){
//                    continue;
//                }
//                WorkCard workCard = new WorkCard();
//                //第一个是列数，第二个是行数
//                String num = "";
//                if (rs.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
//                    Double num1 = rs.getCell(0).getNumericCellValue(); //默认最左边编号也算一列 所以这里得j++
//                    Long num2 = num1.longValue();
//                    num = String.valueOf(num2);
//                }else if(rs.getCell(0).getCellTypeEnum() == CellType.FORMULA){
//                    FormulaEvaluator hssfCell = rwb.getCreationHelper().createFormulaEvaluator();
//                    CellValue tempCellValue = hssfCell.evaluate(rs.getCell(0));
//                    Double num2 = tempCellValue.getNumberValue();
//                    num = String.valueOf(num2);
//                }else {
//                    num = rs.getCell(0).getStringCellValue();
//                }
//
//                workCard.setDueTime(dueTime);
//                workCard.setEffectiveTime(effectiveMonth);
//                if (importType == 1) {
//                    String bh = null;
//                    if (rs.getCell(1) != null && rs.getCell(1).getCellTypeEnum() != null) {
//                        if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
//                            Double bh1 = rs.getCell(1).getNumericCellValue();
//                            Integer bh2 = bh1.intValue();
//                            bh = String.valueOf(bh2);
//                        }else {
//                            bh = rs.getCell(1).getStringCellValue();
//                        }
//                    }else {
//                        resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>编号不能为空</span>");
//                        return resultMap;
//                    }
//                    bh = bh.replaceAll("\\n","");
//                    Staff staff = staffService.getStaffByBh(bh);
//                    if (null == staff) {
//                        resultMap.put("error","序号：" + num + "<br>编号：" + "<span style='color:red;'>" + bh + "<br>错误信息：<span style='color:red;'>编号不存在</span>");
//                        return resultMap;
//                    }
//                    WorkCard workCard1 = workCardService.getWorkCardByStaffId(staff.getId());
//                    if (null == workCard1) {
//                        workCard.setCreateTime(new Date());
//                        workCard.setLuruTime(new Date());
//                        workCard.setStaffId(staff.getId());
//                        workCardService.save(workCard);
//                    }else {
//                        workCard.setId(workCard1.getId());
//                        workCardService.updateById(workCard);
//                    }
//                }else {
//                    String sfz = null;
//                    if (rs.getCell(1) != null && rs.getCell(1).getCellTypeEnum() != null) {
//                        if (rs.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
//                            DecimalFormat df = new DecimalFormat("#");
//                            sfz = String.valueOf(df.format(rs.getCell(1).getNumericCellValue()));
//                        }else {
//                            sfz = rs.getCell(1).getStringCellValue();
//                        }
//                    }else {
//                        resultMap.put("error","序号：" + num + "<br>错误信息：<span style='color:red;'>证件号不能为空</span>");
//                        return resultMap;
//                    }
//                    sfz = sfz.replaceAll("\\n","");
//                    Staff staff = staffService.getStaffInfoBySfz(sfz);
//                    if (null == staff) {
//                        resultMap.put("error","序号：" + num + "<br>证件号：" + "<span style='color:red;'>" + sfz + "<br>错误信息：<span style='color:red;'>证件号不存在</span>");
//                        return resultMap;
//                    }
//                    WorkCard workCard1 = workCardService.getWorkCardByStaffId(staff.getId());
//                    if (null == workCard1) {
//                        workCard.setCreateTime(new Date());
//                        workCard.setLuruTime(new Date());
//                        workCard.setStaffId(staff.getId());
//                        workCardService.save(workCard);
//                    }else {
//                        workCard.setId(workCard1.getId());
//                        workCardService.updateById(workCard);
//                    }
//                }
//            }
//        } catch (Exception e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//            resultMap.put("error", "错误信息：<span style='color:red;'>您上传的excel格式不正确，请检查数据是否包含函数运算或数据格式是否正确</span>");
//            return resultMap;
//        }
//        resultMap.put("sucess","sucess");
//        return resultMap;
//    }

}
